using System;
using System.Data;
using MySql.Data.MySqlClient;


/// <summary>
/// Summary description for Database
/// </summary>
public class FrameworkBase
{
    MySqlConnection objConn;

    #region Abrir
    private void Abrir()
    {

        try
        {
            objConn = new MySqlConnection(@"Data Source=localhost;Initial Catalog=DBLOJA;Persist Security Info=True;User ID=tsm;Password=informatica");
            //objConn = new MySqlConnection(@"Data Source=localhost;Initial Catalog=DBLOJA;Persist Security Info=True;User ID=root;Password=vertrigo");
            //objConn = new MySqlConnection(@"Provider=Microsoft.Jet.MySql.4.0;Data Source=c:\zotto\FrameworkSystem\Framework-Internet\DBFrameworkSYSTEM.mdb");
            objConn.Open();

        }
        catch (Exception ex)
        {
            throw new Exception("Houve um problema ao abrir o Banco de dados: " + ex.Message);
        }
    }
    #endregion

    #region ExecutarQuery
    public DataTable ExecutarQuery(string sComando)
    {

        try
        {

            Abrir();
            MySqlCommand cmd = objConn.CreateCommand();

            cmd.CommandText = sComando;

            MySqlDataAdapter dr = new MySqlDataAdapter(cmd);
            DataSet dsRetorno = new DataSet();
            dr.Fill(dsRetorno);

            DataTable dtRetorno = dsRetorno.Tables[0];

            return dtRetorno;

        }
        catch (Exception ex)
        {
            throw new Exception("Problemas ao executar a Query: " + ex.Message);
        }
        finally
        {
            objConn.Close();
        }
    }
    #endregion

    #region ExecutarQueryDS
    public DataSet ExecutarQueryDS(string sComando)
    {

        try
        {

            Abrir();
            MySqlCommand cmd = objConn.CreateCommand();

            cmd.CommandText = sComando;

            MySqlDataAdapter dr = new MySqlDataAdapter(cmd);
            DataSet dsRetorno = new DataSet();
            dr.Fill(dsRetorno);

            return dsRetorno;

        }
        catch (Exception ex)
        {
            throw new Exception("Problemas ao executar a Query: " + ex.Message);
        }
        finally
        {
            objConn.Close();
        }
    }
    #endregion

    #region Executar
    public int Executar(string sMySql)
    {
        try
        {
            Abrir();
            MySqlCommand comando;
            comando = new MySqlCommand(sMySql, objConn);
            comando.CommandText = sMySql;
            int intRetorno = comando.ExecuteNonQuery();
            return intRetorno;
        }
        catch (Exception ex)
        {
            throw new Exception("Problemas ao executar o comando: " + ex.Message);
        }
        finally
        {
            objConn.Close();
        }
    }
    #endregion

    #region ExecutarQueryDA
    public MySqlDataAdapter ExecutarQueryDA(string sComando)
    {

        try
        {

            Abrir();
            MySqlCommand cmd = objConn.CreateCommand();

            cmd.CommandText = sComando;

            MySqlDataAdapter da = new MySqlDataAdapter(cmd);

            return da;

        }
        catch (Exception ex)
        {
            throw new Exception("Problemas ao executar a Query: " + ex.Message);
        }
        finally
        {
            objConn.Close();
        }
    }
    #endregion

    #region ProximoCodigo
    public int ProximoCodigo(string strTabela)
    {

        try
        {

            Abrir();
            MySqlCommand cmd = objConn.CreateCommand();

            string strMySql = "Select max(Codigo) + 1 " +
                           "From " + strTabela;

            cmd.CommandText = strMySql;

            MySqlDataAdapter dr = new MySqlDataAdapter(cmd);
            DataSet dsRetorno = new DataSet();
            dr.Fill(dsRetorno);

            DataTable dtRetorno = dsRetorno.Tables[0];

            if (dtRetorno.Rows[0][0].ToString() != string.Empty)
                return Convert.ToInt32(dtRetorno.Rows[0][0].ToString());
            else
                return 1;

        }
        catch (Exception ex)
        {
            throw new Exception("Problemas ao executar a Query: " + ex.Message);
        }
        finally
        {
            objConn.Close();
        }
    }
    #endregion

    
}

